/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package pckSQL;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import pckFiles.Encodage;

/**
 *
 * @author Portable-Maison
 */
public class SQL_Etudiant extends SQL_Connection {

    public SQL_Etudiant(String psDB_Name, String psDB_Adresse, String psDB_User, String psDB_Password)
    {
        super(psDB_Name,psDB_Adresse, psDB_User, psDB_Password);
    }

  public int getID(String psTableName)
    {
    Statement stmt = null;
    ResultSet rs = null;
    int liI = 0;
    String SQL = "SELECT * FROM " + psTableName + ";";
    try
    {
    stmt = gaConnection.createStatement();
    rs = stmt.executeQuery(SQL);
    while (rs.next())
        {
        liI = rs.getInt (1);
        }
    liI++;
    rs.close ();
    }
    catch (SQLException ex)
    {
        Logger.getLogger(SQL_Connection.class.getName()).log(Level.SEVERE, null, ex);
    }
    return liI;
    }

    public int validerConnection(String psMatricule, String psPassword)
    {
    Statement stmt = null;
    ResultSet rs = null;
    int liEtat = -1;
   
    int liI = 0;
    String SQL = "SELECT * FROM etudiant;";
    try
    {
    stmt = gaConnection.createStatement();
    rs = stmt.executeQuery(SQL);
    while (rs.next())
        {
        int liPos = rs.getInt ("idEtudiant");
        String lsMatricule = rs.getString ("MatriculeEtudiant");
        String lsPassword;
        Encodage laEncode = new Encodage(rs.getString ("motPasseEtudiant"),1);
        lsPassword = laEncode.EncToString ();
        if ((lsMatricule.equals (psMatricule)) & (lsPassword.equals (psPassword)))
            {
            liEtat = liPos;
            }
        }
    rs.close ();
    }
    catch (SQLException ex)
    {
        Logger.getLogger(SQL_Connection.class.getName()).log(Level.SEVERE, null, ex);
    }
    return liEtat;
    }

    public boolean ajouterUtilisateur(String psPrenom,
                                      String psNom,
                                      String psMatricule,
                                      String psPassword,
                                      int piProg)
    {
    Statement stmt = null;
    int idStudent;
    idStudent = getID("etudiant");
    String SQL = ("INSERT INTO etudiant" +
                 " VALUES ('"+idStudent+"','" + piProg + "','" + psNom + "','" + psPrenom + "','" +
                  psMatricule +"','" + psPassword + "');");

   try {
            stmt = gaConnection.prepareStatement (SQL);
            stmt.executeUpdate (SQL);
           // rs = stmt.executeQuery (SQL);
            System.out.println("Utilisateur a été ajouté avec succès");
            stmt.close ();
            return true;
        }
        catch (SQLException ex) {
            Logger.getLogger (SQL_Connection.class.getName()).log (Level.SEVERE, null, ex);
            return false;
        }
    }
    
}
